Relational and object-relational database management systems store information in tables of rows in a database. To retrieve data, queries that request data are submitted to a database server, which computes the queries and returns the data requested.
Query statements submitted to the database server should conform to the syntactical rules of a particular query language. One popular query language, known as the Structured Query Language (SQL), provides users a variety of ways to specify information to be retrieved.
A query submitted to a database server is analyzed by a query optimizer. Based on the analysis, the query optimizer generates an execution plan optimized for efficient execution. The optimized execution plan may be based on a rewrite of the query.
In one type of inefficient queries, a complex query contains a subquery in a HAVING clause where the subquery can be subsumed by an outer query. When a subquery in the HAVING clause of a complex query can be subsumed by the outer query of the complex query, but it is not removed by some technique, the result is a sub-optimal query execution plan that performs unnecessary and duplicative table accesses and join operations.
This type of inefficient queries occurs for many reasons. The first reason is that database users or application developers often do not write queries directly, but utilize database tools. Such database tools automatically generate queries based on the declarative input received from the user. In addition, even a human application developer may introduce these kinds of subqueries because he is not aware of the entirety of the intricacies of query transformation and optimization.
Therefore, it is desirable to develop techniques for rewriting queries to eliminate subqueries from HAVING clauses, where such subqueries can be subsumed by the outer query.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.